APDL Eductation Resources/APDL Eductation Resources.iso/programs/database/powerbase/docs/Validate

Text File | 1997-02-21 | 15.1 KB | 285 lines

            CH 5 − INPUT VALIDATION AND VALIDATION TABLES
            =============================================
Powerbase can apply two kinds of data input validation:−

•       Character validation
•       Table validation

5.1 Character validation
========================
This means exercising control over what characters a field will accept. RISC
OS provides the means to allow writable icons to accept only certain
characters, or ranges of characters, and reject others. The disallowed
characters are simply ignored so that attempts to type them have no effect.

If you open Powerbase’s own application directory (Shift/double-click) you
will find a file called ValStrings. You are recommended not to alter this
file unless you understand exactly what you are doing and, if you do so, to
keep a copy of the original. Examining it, however, can give you an insight
into the validation process. Look, for example, at the entry “03 Number”.
This refers to field type 3 (Numeric). The line below is the validation
string of the writable icon which makes up a numeric field. It looks like
this:−

                Pptr_write,4,4;A0-9.+\-

The initial “P” is a command and signifies “pointer”. “ptr_write” is the
name of the sprite used to represent the mouse pointer when it is over this
type (and many other types) of field. You will have noticed that the usual
RISC OS arrow changes to a blue vertical bar, somewhat resembling the caret,
when over a writable field. “4,4” specifies the “active point” of the
pointer. The next character is a semicolon which indicates that a new
command follows.

The “A” which comes next is the command “Allow” and is the part of the
validation string which especially concerns us. It determines what
characters the field will allow you to type. In this case they are the
numerals 0-9, the decimal point and the plus and minus signs. Why the
back-slash before the latter? Because “-” is a special character in an icon
validation string (used, as in this case, to specify a range of characters)
and so are the semicolon, tilde (~) and the backslash itself. To include any
of these four characters in the validation string you must precede it with a
backslash.

A couple of further examples should be sufficient. “01 Alphanumeric” uses
the same pointer type but the characters accepted are <space> (immediately
after the “A” for “allow”), the upper-case letters A-Z, the lower-case
letters a-z, the numerals 0-9, some common punctuation and the “½”
character. You might want to add to this list. “06 Calculated” uses a
different pointer sprite (ptr_calc, which resembles a pocket calculator) and
has no “Allow” command at all. (You can’t type into this type of field; its
value is calculated from other fields.)

5.2 Validation tables
=====================
Table validation is used where a record field is only allowed to have
certain “values”. A manufacturing company, for instance, will have a product
code for every item it makes and a database of customers which the firm
supplies will make use of such codes to identify the items. It is essential
that whoever enters data is prevented from typing a spurious code. This can
be achieved by linking the appropriate field to a validation table. Fields
linked in this way are recognizable by having their text dark green instead
of black. Powerbase will allow you to type invalid data into the field
initially but, when you click the mouse over a new field or type Return, the
linked validation table is scanned to see whether what you entered in the
field is on the allowed list. If it isn’t Powerbase will restore the
previous contents of the field and print an error message.

Validation tables have other uses too. They can have extra columns
containing other data about the products, such as a name and a brief
description, and lists can be printed in which this more understandable data
appears instead of the codes.  You can also have Powerbase replace the
typed-in code with a longer, more readable, form immediately on entry.
Provided the substitute data will fit in the field replacement occurs when
you type Return or click the mouse in another field. If the replacement is
too long then the typed-in data will be unchanged.  This can be a great
timesaver when a lot of data has to be entered.

The F.E. college database referred to in 2.2.4 makes extensive use of
validation tables for subjects, courses, tutors and schools of origin. Not
only does this trap typing errors but the data entry is greatly speeded up
when one can type ELL instead of English Language and Literature, yet have
the latter printed out on a list by selecting the Expand switch in the Print
options window.

5.3  Creating a validation table
================================
Choose Create table from the Validation submenu. Give the table a suitable
name and enter the number of rows required. This will be the maximum number
of items on the allowed list. (It is possible to increase the number later
if necessary.) Next place the caret in the first row (row 0) of the
scrolling list and enter the width (in characters) and the heading
(optional) for the first column of the validation table.

If the table is to be used to constrain data entry to a allowed list one
column may be all you need. The most common situation, however, is to have
two columns, the first holding the allowed list and the second being an
expansion or explanation of the former. Use the next row of the scrolling
list to enter the column width and heading for the second table column.  It
is possible to use very large and complex validation tables with all kinds
of data associated with each item on the allowed list.  There is room for
information about 20 columns, but even this can be increased if necessary
(see 14.7).

You can, if you wish, choose the foreground and background colours of both
the heading and the body of your table by clicking repeatedly with SELECT or
ADJUST on the Heading and Data icons. Whether this changes the foreground or
background colour depends on which of the two radio buttons is selected.

When you are satisfied with the data click Create and the table will be
created in memory. It is not saved on disc at this stage.  You may enter
data into it now or later. Entries may be freely altered and overwritten.
The table will be saved when you close the database or quit Powerbase but,
if you want to play safe, click Force update on the keypad.

5.4  Linking tables to fields
=============================
To link your newly-created validation table to a field click MENU over the
field and choose Link to table from the Field submenu.*  A pair of “bump”
icons, with the usual pop-up menu alternative, lets you cycle through the
tables in memory.  When you have the name of the required table displayed
choose the column of the table to which the field is to be linked. It is
recommended that column 0 normally provide the link (and therefore contain
the data items for the allowed list) and this is set by default. You may,
however, link to any column in the table and another pair of bump icons lets
you cycle through the column numbers.

Select Linked to table and click on OK. You will now see that the foreground
colour of the linked field has changed from black to green. Place the caret
in the field and click on List values on the keypad. The validation table
should be displayed.

5.4.1 The Replace on entry feature
----------------------------------
If you select this switch before linking the table a third pair of bump
icons becomes active, allowing you to choose which column of the table will
replace the data which you type in. In the F.E. college database referred to
in 5.2 the short subject codes (e.g. CHE) would be in the first column and
the longer names (e.g. Chemistry) in the second. On typing CHE <Return>
Powerbase would replace it with Chemistry. In such cases both the entries in
the allowed list proper and those in the replacement list are considered
equally valid and either may be typed. you could actually type Chemistry if
you wished, instead of just CHE, but the former will obviously take longer
to enter and you are far more likely to make a mistake (in which case
Powerbase would object).

5.4.2 When to turn off the Exact match switch
---------------------------------------------
The Exact match switch is normally selected to indicate that the only user
inputs which will be accepted are those which exactly match an entry on the
allowed list.  Deselecting the switch allows you to type inputs which are
longer (but not shorter) than the entries on the allowed list so long as the
leftmost part of the input matches such an entry. The unmatched “tail” of
your typed entry will be attached to the end of the replacement string. This
feature is intended for use with the Replace on entry facility. Suppose your
database records numbers of items called Widgets, Doodahs and Thingummies.
You decide to identify these names with the codes W, D and T, put the codes
in the first column of the table and the names in the second and select
Replace on entry. You can then type W <Return> and it will be replaced with
Widgets. What you might want, however, is to type W7 and have it replaced
with Widgets, 7.  Deselecting Exact match allows you to do just that since
the W in W7 matches an entry in the validation list. The “expanded” entry in
the second column is made to read “Widgets, ” and Powerbase tacks the
unmatched part of your entry onto the end of this so that W7 is replaced
with Widgets, 7.

5.5  Displaying validation tables
=================================
Placing the mouse pointer over a linked record field and double-clicking
with SELECT makes a small window pop up to the right of the field.  This
shows all the data which is on the same row of the validation table as the
linked item. The item from the allowed list is highlighted in green and the
item (if any) to be substituted on entry is shown in red.  This feature is
very useful if you are examining a database which uses coded data and you
encounter a code which you haven’t seen before.

You can also make this window appear automatically whenever the caret enters
a linked field. To turn on this feature choose Preferences from the icon-bar
menu and select the Display linked table data switch.

When linked to a field in the database record the complete table may be
displayed by placing the caret in the linked field and clicking on the
keypad button List values (F9). Alternatively, any table present in memory,
whether linked to a data field or not, may be displayed by selecting it from
the Display table submenu (reached from the Validation submenu).

5.6  The validation table menu
==============================
Clicking MENU over a displayed table offers a menu with entries as follows:−

Clear
-----
Removes all data from the table, leaving it blank. Since wiping out a
table in this way is pretty drastic you will be asked to confirm the
operation before it actually takes place. When you close a database all the
validation tables in memory are written to the disc so you will over-write
your disc copy with the blank table.  It is possible to recover the table
using Undo all provided the table has not been closed.

Save
----
Leads to a Save box in which the default pathname points to a directory
called ValTables inside the database application directory. Since that’s
where Powerbase expects to find the tables for a database you should
normally accept this pathname by clicking on Save or typing Return. Only if
you are transferring tables from one database to another should you need to
drag the icon to a filer window. All validation tables in memory are, in any
case, saved to the ValTables directory when you close the database or quit
Powerbase and you can also make this happen at any time by clicking on Force
update on the keypad.

Print
-----
Outputs the contents of a validation table in the same format as a
report. The output appears in a window from which it may be saved as a text
file (see 3.1.1).

Sort column <n>
---------------
Will sort the table on the nth column, that being the one
the mouse pointer was over when you clicked MENU on the table.

Undo change
-----------
Will restore the specific item which you were pointing at when
you clicked MENU to the state it was in when the table was displayed.

Undo all
--------
Restores the entire table to the state it was in when first
displayed, even if Clear has been used. You cannot undo changes if you have
closed the table then re-displayed it.

Save as CSV
-----------
Leads to a Save box from which the table contents can be saved
as a CSV file. The default pathname points to the database’s PrintJobs
directory.

Modify
------
Brings up the same window as you used to create the table. You may
then increase the number of rows, add extra columns, alter the order of
existing columns or, indeed, do anything sensible. Be careful about renaming
columns; combining this with shuffling the columns about is apt to cause
confusion − Powerbase isn’t a mind-reader! When you have made the required
changes click on  Modify.

5.7  Loading validation tables
==============================
A table linked to a field is automatically loaded when the database is
opened. If not yet linked, however, the table won’t be in memory unless you
have just created it. To load the table choose Show table files from the
Validation submenu (Ctrl Q). The contents of the ValTables directory will be
displayed and the required tables can dragged onto the record window (or
simply double-clicked). As each table is dropped it will be displayed.

There may be times when the user wishes to use a validation table to hold
some data, but doesn’t want to use it to validate input into a field or to
link it to a field at all. It is possible to load a validation table once
the database is open by using the procedure described in the previous
paragraph, but a more convenient solution is to add a “!” character to the
end of the table’s name. Powerbase will then load it whether any field is
linked to it or not.

5.8  Including validation table data in printouts
=================================================
Validation tables are often used to allow short codes to be entered in
records but with a link to a more descriptive entry in another column of the
table.  If this is all that is required then always put the data which makes
up the actual allowed list, i.e. the items which are allowed in the fields
of the main record, into the first column of the table and set the link from
the record field to this column (column 0). The more detailed “expanded”
entry should go in column 1. Printing with the Expand switch in the Print
options window selected  will then print the column 1 instead of the column
0 entry.

You can, however, include data from other columns of a table by displaying
the table  and clicking in the required columns with ADJUST, exactly like
selecting main record fields for printing (see 3.3.1). It is immaterial
which row you click on; only the column matters and the highlighting to show
which columns are selected always appears in the first row. The columns
selected in all tables are saved as part of a print selection and may
therefore be retrieved for future use. As well as highlighting the required
columns you will also need to select the Expand switch as described above.
Printed reports will then include all the data from the highlighted columns.